import * as mysql from 'mysql';
import Crypto from './crypto';

function logErr(funName){
    const path = 'utils->db.ts->';
    return (msg) => {
        console.log(path + funName + '()->错误信息：' + msg);
    };
}

export default class Db{
    static pool: mysql.IPool;

    /**
     * 初始化数据连接池
     * @param config 
     */
    static init(config: mysql.IPoolConfig){
        this.pool = mysql.createPool({
            host: '127.0.0.1',
            user: config.user,
            password: config.password,
            database: config.database,
            port: config.port,
        });
    }

    /**
     * 执行sql语句
     */
    static query<T>(sql: string): Promise<T[]>{
        return new Promise((resolve, reject) => {
            this.pool.getConnection((err, conn) => {
                if (err) {
                    reject(err);
                }
                else {
                    conn.query(sql, (err, rows, field) => {
                        conn.release();
                        if (err) {
                            reject(err);
                        }
                        else {
                            resolve(rows);
                        }
                    });
                }
            });
        });
    }

    /**
     * 使用account在t_users中获取用户数据
     */
    static async get_user_data(account){
        const sql = 'SELECT userId,account,name,lv,coins,gems,roomId FROM t_users WHERE account = "' + account + '"';
        const dataArr = await this.query<t_users>(sql);
        if(dataArr.length == 0){
            return null;
        }
        const data = dataArr[0];
        data.name = Crypto.fromBase64(data.name);
        return data;
    }


    /**
     * 使用userId在t_users表中获取roomId
     */
    static async get_room_id_of_user(userId){
        const sql = `SELECT roomId FROM t_users WHERE userId = "${userId}"`;
        const dataArr = await this.query<t_users>(sql);
        const data = dataArr[0];
        return data.roomId;
    }

    /**
     * 使用roomId在t_rooms中查询是否有房间信息（检查房间是否还在开着）
     */
    static async is_room_exist(roomId){
        const sql = `SELECT * FROM t_rooms WHERE id = "${roomId}"`;
        const dataArr = await this.query<t_rooms>(sql);
        return dataArr.length > 0;
    }
    

    /**
     * 使用userId更新t_users的roomId
     */
    static async set_room_id_of_user(userId, roomId){
        if(roomId != null){
            roomId = `"${roomId}"`;
        }
        const sql = `UPDATE t_users SET roomId = ${roomId} WHERE userId = "${userId}"`;
        const dataArr = await this.query<t_users>(sql);
        return dataArr;
    }

    /**
     * 使用account在t_users表中查找用户是否已经存在
     */
    static async is_user_exist(account){
        const sql = `SELECT userId FROM t_users WHERE account = "${account}"`;
        const data = await this.query(sql);
        return data.length > 0;
    }


    /**
     * 创建用户写入t_users表中  
     * 返回值  0->插入数据正常完成 2->插入数据时发生错误  1->前4个参数有为空的值
     * @param account 用户标识(时间戳生成的)
     * @param name 用户昵称
     * @param coins 金币数量
     * @param gems 宝石数量
     * @param sex 性别
     * @param headimg 头像缩略图 
     * @return {number} 0->插入数据正常完成 2->插入数据时发生错误  1->前4个参数有为空的值
     */
    static async create_user(account, name, coins, gems, sex, headimg){
        if(account == null || name == null || coins == null || gems == null){
            return 1;
        }

        headimg = headimg ? `"${headimg}"` : 'null';
        name = Crypto.toBase64(name);
        let sql = `INSERT INTO t_users(account,name,coins,gems,sex,headimg) VALUES("{0}", "{1}", {2}, {3}, {4}, {5})`;
        sql = sql.format(account, name, coins, gems, sex, headimg);
        const dataArr = await this.query<t_users>(sql);
        return dataArr ? 0 : 2;
    }


    /**
     * 使用account在t_users表中查找gmes信息并返回
     * @param account 
     */
    static async get_gems(account){
        const sql = `SELECT gems FROM t_users WHERE account = "${account}"`;
        const dataArr = await this.query<t_users>(sql);
        return dataArr[0].gems;
    }


    /**
     * 使用type在t_message表找查找版本号公告信息并返回
     */
    static async get_messgae(type, version?){
        let sql = `SELECT * FROM t_message WHERE type = "${type}"`;
        if(version){
            sql += ' AND version != "${version}"';
        }
        const dataArr = await this.query<t_message>(sql);
        return dataArr[0];
    }

    /**
     * 将创建房间的信息写入数据库
     * @param roomId 
     * @param conf 
     * @param ip 
     * @param port 
     * @param create_time 
     */
    static async create_room(roomId, conf, ip, port, create_time){
        let sql = `INSERT INTO t_rooms(uuid,id,base_info,ip,port,create_time) VALUES('{0}','{1}','{2}','{3}',{4},{5})`;
        const uuid = Date.now() + roomId;
        const baseInfo = JSON.stringify(conf);
        sql = sql.format(uuid, roomId, baseInfo, ip, port, create_time);

        const dataArr = await this.query<t_rooms>(sql);
        return roomId;
    }


    /**
     * 使用roomId在t_rooms表中寻找到运行这个房间的IP跟PORT
     */
    static async get_room_addr(roomId){
        const sql = `SELECT ip, port FROM t_rooms WHERE id = "${roomId}"`;
        const dataArr = await this.query<t_rooms>(sql);
        if(dataArr.length){
            return {
                ip: dataArr[0].ip,
                port: dataArr[0].port,
            };
        }
    }

    /**
     * 使用roomId查询t_rooms返回所有的数据
     */
    static async get_room_data(roomId){
        if(roomId == null){
            return;
        }

        const sql = `SELECT * FROM t_rooms WHERE id = "${roomId}"`;
        const dataArr = await this.query<t_rooms>(sql);
        if(dataArr.length == 0){
            return;
        }

        dataArr[0].user_name0 = Crypto.fromBase64(dataArr[0].user_name0);
        dataArr[0].user_name1 = Crypto.fromBase64(dataArr[0].user_name1);
        dataArr[0].user_name2 = Crypto.fromBase64(dataArr[0].user_name2);
        dataArr[0].user_name3 = Crypto.fromBase64(dataArr[0].user_name3);
        return dataArr[0];
    }


    /**
     * 更新t_rooms内的用户信息
     */
    static async update_seat_info(roomId, seatIndex, userId, icon, name){
        let sql = `UPDATE t_rooms SET user_id{0} = {1},user_icon{0} = "{2}",user_name{0} = "{3}" WHERE id = "{4}"`;
        name = Crypto.toBase64(name);
        sql = sql.format(seatIndex, userId, icon, name, roomId);
        const dataArr = await this.query(sql);
        return dataArr;
    }


    static async update_next_button(roomId, nextButton){
        let sql = 'UPDATE t_rooms SET next_button = {0} WHERE id = "{1}"';
        sql = sql.format(nextButton, roomId);
        const data = await this.query(sql);
        return data;
    }


    static async get_user_history(userId){
        const sql = `SELECT history FROM t_users WHERE userId = "${userId}"`;
        const data = await this.query<t_users>(sql);
        if(!data){
            return false;
        }

        if(data.length == 0){
            return false;
        }
        
        const history = data[0].history;
        if(!history){
            return false;
        }

        return JSON.parse(history);
    }

    static async update_user_history(userId, history){
        history = JSON.stringify(history);
        const sql = `UPDATE t_users SET roomId = null, history = '${history}' WHERE userId = "${userId}"`;
        const data = await this.query(sql);
        return data;
    }


    /**
     * 删除房间信息
     * @param roomId 
     */
    static async delete_room(roomId){
        let sql = `DELETE FROM t_rooms WHERE id = '{0}'`;
        sql = sql.format(roomId);
        const data = await this.query(sql);
        return data;
    }


    /**
     * 用uuid从t_games表中复制一条数据插入到t_games_archive表中并删除t_games表中这一行的数据
     */
    static async archive_games(room_uuid){
        const log = logErr('archive_games');

        let sql = `INSERT INTO t_games_archive SELECT * FROM t_games WHERE room_uuid = '{0}'`;
        sql = sql.format(room_uuid);
        const data = await this.query(sql);
        if(data == null){
            log('插入数据失败');
            return;
        }

        const data1 = await this.delete_games(room_uuid);
        return data1;
    }

    static async delete_games(room_uuid){
        const log = logErr('delete_games');

        let sql = `DELETE FROM t_games WHERE room_uuid = '{0}'`;
        sql = sql.format(room_uuid);
        const data = await this.query(sql);
        if(data == null){
            log('删除游戏数据失败');
            return;
        }

        return data;
    }


    static async create_game(room_uuid, index, base_info){
        const log = logErr('create_game');

        let sql = `INSERT INTO t_games (room_uuid, game_index, base_info, create_time) VALUES('{0}', {1}, '{2}', unix_timestamp(now()))`;
        sql = sql.format(room_uuid, index, base_info);
        const data: any = await this.query(sql);
        if(data == null){
            log('插入数据失败');
            return;
        }

        return data.insertId;

    }

    /**
     * 更新t_games中result的数据
     */
    static async update_game_result(room_uuid, index, result){
        result = JSON.stringify(result);
        const sql = `UPDATE t_games SET result = "${result}" WHERE room_uuid = "${room_uuid}" AND game_index = "${index}"`;
        const data = await this.query(sql);
        return data;
    }

    /**
     * 更新t_games表中的action_records
     */
    static async update_game_action_records(room_uuid, index, actions){
        const sql = `UPDATE t_games SET action_records = "${actions}" WHERE room_uuid = "${room_uuid}"`;
        const data = await this.query(sql);
        return data;
    }

    /**
     * 更新游戏局数
     */
    static async update_num_of_turns(roomId, numOfTurns){
        let sql = 'UPDATE t_rooms SET num_of_turns = {0} WHERE id = "{1}"';
        sql = sql.format(numOfTurns, roomId);
        const data = await this.query(sql);
        return data;
    }


    /**
     * 更新gems
     */
    static async cost_gems(userId, cost){
        const sql = `UPDATE t_users SET gems = gems - ${cost} WHERE userId = ${userId}`;
        const data = await this.query(sql);
        return data;
    }
}



